#encoding 'utf-8'
load 'rds_api.rb'
@rds = @rds_tt

#用于将客服企业微信中的跟进记录和客户档案导入商城系统

#截至2023-02-29
#SELECT count(*) FROM `eb_user` where mark = '' and pay_count > 0; #当前共有1294位购买过的客户没有建档
#SELECT count(*) FROM `eb_user` where mark = ''; #当前共有10455位用户没有建档
#运行后，共有1168/10355位客户未建立诊断
#截至2023-02-28记录，在description中准确记录了uid的共有679，未记录的为100, 再检查以便
def import_user_profiles xlsx="user_profiles.xlsx"

    puts "Importing profiles from exited xlsx file\n"

    fields = ['nickname', 'last_contact', 'remark', 'phone', 'description', 'stage', 'channel', 'region', 'rep' ]
    recs = get_xlsx_recs_from xlsx, fields

    rec_results = {}
    rec_idx = 1
    recs.each do |rec|
        rec.store 'idx', rec_idx
        description = rec['description']
        if !description.to_s.empty?
            uid = description.split('#')[1].to_s.split('，')[0].to_s.to_i
            if uid == 0 #说明格式里没有uid
                #print 'x'
                #puts "[#{sprintf('%05d',uid)}] #{description}"
                rec.store 'TTS-IMPORT', 'SKIPPED - INVALID UID'
            else
                #print '.'
                #puts "[#{sprintf('%05d',uid)}] #{description}"
                #isql = "select uid, mark from tutem_mall.eb_user where uid = #{uid}"
                #(queryRds(isql)).each { |i| puts "tts:" + i['mark'] + "css:" + description }
                usql = "update tutem_mall.eb_user set mark = '#{@rds.escape description}' where uid = #{uid}"
                queryRds(usql)
                rec.store 'TTS-IMPORT', 'SUCCESS'
            end
        else
            rec.store 'TTS-IMPORT', 'SKIPPED - NO PROFILE'
        end
        rec_results.store rec_idx, rec
        rec_idx += 1
    end

    rfns = xlsx.split('.')[0].to_s + '_import_results'       #取输入文件的前缀
    dump_hash_to_excel rec_results, rfns


end
#import_user_profiles

#导入所有客服对话记录
def import_service_chats xlsx="service_chats.xlsx"

    puts "Importing profiles from exited xlsx file\n"

    fields = ['employee', 'short_name', 'phone', 'contact_time', 'contact_info' ]
    recs = get_xlsx_recs_from xlsx, fields

    rec_results = {}
    rec_idx = 1
    recs.each do |rec|

        rec.store 'idx', rec_idx
        uid = 0
        str = rec['short_name'].split('#')[1].to_s
        size = str.length
        uid_str = ''
        for i in 0..size do
            break if !['0','1','2','3','4','5','6','7','8','9'].include?(str[i])
            uid_str += str[i]
        end

        uid = uid_str.to_i
        if uid > 0

            aid = 0
            aid = 20 if rec['employee'].include? '妍妍'

            add_time = (Time.parse(rec['contact_time'])).to_i
            day_str = Time.at(add_time).strftime('%Y-%m-%d').to_s
            title = day_str + " 跟进情况 A##{aid}"

            isql = "select * from tutem_mall.eb_user_profile where title like '%#{day_str}%A##{aid}%'"
            already_recorded = false
            (queryRds(isql)).each do |i|
                already_recorded = true
                break
            end

            if already_recorded
                rec.store 'TTS-IMPORT', "SKIPPED - ALREADY RECORDED"
            else

                memo = rec['contact_info']
                memo = memo.gsub('备注：','')
                memo = memo.gsub('备注:','')
                memo = memo.gsub(' ','')
                usql = "INSERT INTO `tutem_mall`.`eb_user_profile` (`uid`, `atype`, `aid`, `type`, `title`, `memo`, `pid`, `is_hide`, `add_time`) 
                VALUES (#{uid}, 'admin', #{aid}, 'tracking_report', '#{title}', '#{@rds.escape memo}', 0, 0, #{add_time}); #rec_idx : #{rec_idx}"
                queryRds usql
                rec.store 'TTS-IMPORT', "SUCCESS - UID##{uid}"
            end
        else
            rec.store 'TTS-IMPORT', 'SKIPPED - INVALID UID'
        end

        rec_results.store rec_idx, rec
        rec_idx += 1

    end

    rfns = xlsx.split('.')[0].to_s + '_import_results'       #取输入文件的前缀
    puts "results dumped to #{rfns}"
    dump_hash_to_excel rec_results, rfns

end
#import_service_chats
